﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;


public partial class clsDatabase : System.Web.UI.Page
{
    private OracleConnection objConn;
    private OracleCommand objCmd;
    private OracleTransaction Trans;
    private String strConnString;

    public clsDatabase(String strConn)
    {
        strConnString = ConfigurationManager.ConnectionStrings[strConn].ConnectionString; ; 
    }

    public OracleDataReader QueryDataReader(String strSQL, String strParam, String strParam2)
    {
        OracleDataReader dtReader;
        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();

        objCmd = new OracleCommand(strSQL, objConn);
        string[] arrayParam = null;
        string[] arrayParam2 = null;

        if (strParam != "")
        {
            arrayParam = strParam.Split('|');
            arrayParam2 = strParam2.Split('|');

            if (arrayParam.Length > 0)
                for (int i = 0; i < arrayParam.Length; i++)
                    objCmd.Parameters.Add(new OracleParameter(arrayParam[i], arrayParam2[i]));
        }

        dtReader = objCmd.ExecuteReader();
        objConn.Close();
        objConn = null;
        return dtReader; //*** Return DataReader ***//

    }

    public DataSet QueryDataSet(String strSQL, String strParam, String strParam2)
    {
        DataSet ds = new DataSet();
        OracleDataAdapter dtAdapter = new OracleDataAdapter();
        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();

        objCmd = new OracleCommand();
        objCmd.Connection = objConn;
        objCmd.CommandText = strSQL;
        objCmd.CommandType = CommandType.Text;
        
        string[] arrayParam = null;
        string[] arrayParam2 = null;

        if (strParam != "")
        {
            arrayParam = strParam.Split('|');
            arrayParam2 = strParam2.Split('|');

            if (arrayParam.Length > 0)
                for (int i = 0; i < arrayParam.Length; i++)
                    objCmd.Parameters.Add(new OracleParameter(arrayParam[i], arrayParam2[i]));
        }

        dtAdapter.SelectCommand = objCmd;
        dtAdapter.Fill(ds);
        objConn.Close();
        objConn = null;
        return ds;   //*** Return DataSet ***//

    }

    public DataTable QueryDataTable(String strSQL)
    {
        OracleDataAdapter dtAdapter;
        DataTable dt = new DataTable();
        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();

        dtAdapter = new OracleDataAdapter(strSQL, objConn);
        dtAdapter.Fill(dt);
        objConn.Close();
        objConn = null;
        return dt; //*** Return DataTable ***//

    }

    public Boolean QueryExecuteNonQuery(String strSQL,String strParam,String strParam2)
    {

        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();
      
        string[] arrayParam = null;
        string[] arrayParam2 = null;

        arrayParam=strParam.Split('|');
        arrayParam2=strParam2.Split('|');
        
        try
        {

            objCmd = new OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandType = CommandType.Text;
            objCmd.CommandText = strSQL;
            
            if (arrayParam.Length>0)
            for (int i = 0; i < arrayParam.Length;i++ )
                objCmd.Parameters.Add(new OracleParameter(arrayParam[i], arrayParam2[i]));
            
            objCmd.ExecuteNonQuery();
            objConn.Close();
            objConn = null;
           
            return true; //*** Return True ***//
        }
        catch (Exception)
        {
            objConn.Close();
            objConn = null;
            return false; //*** Return False ***//

        }
    }


    public Object QueryExecuteScalar(String strSQL)
    {
        Object obj;
        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();

        try
        {
            objCmd = new OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandType = CommandType.Text;
            objCmd.CommandText = strSQL;

            obj = objCmd.ExecuteScalar();  //*** Return Scalar ***//
            objConn.Close();
            objConn = null;
            return obj;
        }
        catch (Exception)
        {
            objConn.Close();
            objConn = null;
            return null; //*** Return Nothing ***//
        }
    }

    public void TransStart()
    {
        objConn = new OracleConnection();
        objConn.ConnectionString = strConnString;
        objConn.Open();
        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted);
        objConn.Close();
        objConn = null;
    }


    public void TransExecute(String strSQL)
    {
        objCmd = new OracleCommand();
        objCmd.Connection = objConn;
        objCmd.Transaction = Trans;
        objCmd.CommandType = CommandType.Text;
        objCmd.CommandText = strSQL;
        objCmd.ExecuteNonQuery();
        objConn.Close();
        objConn = null;
    }


    public void TransRollBack()
    {
        Trans.Rollback();
    }

    public void TransCommit()
    {
        Trans.Commit();
    }

    public void Close()
    {
        objConn.Close();
        objConn = null;
    }
}